'''
Some commands to use our mappers:
In [1]: from quicksql.model.relation import Person
In [2]: mr_jones = Person('Tom', 'Jones', 'tom.jones@test.com')
In [3]: meta.Session.save(mr_jones)
In [4]: from quicksql.model.relation import Address
In [5]: new_address = Address()
In [6]: new_address.address = '33 Pine Marten Lane, Pleasantville'
In [7]: mr_jones.my_addresses.append(new_address)
In [8]: meta.Session.commit()
In [9]: person_q = meta.Session.query(Person)
In [10]: mr_jones = person_q.filter(Person.lastname=='Jones').one()
In [11]: print mr_jones
<Person Tom Jones>
In [12]: print mr_jones.my_addresses[0].address
33 Pine Marten Lane, Pleasantville
'''

import sqlalchemy as sa
from sqlalchemy import orm

from quicksql.model import meta

t_people = sa.Table('people', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('firstname', sa.types.String(100)),
    sa.Column('lastname', sa.types.String(100)),
    sa.Column('email', sa.types.String(100))
)

t_addresses_people = sa.Table('addresses_people', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('person_id', sa.types.Integer, sa.ForeignKey('people.id')),
    sa.Column('address_id', sa.types.Integer, sa.ForeignKey('addresses.id'))
)

t_addresses = sa.Table('addresses', meta.metadata,
    sa.Column('id', sa.types.Integer, primary_key=True),
    sa.Column('address', sa.types.String(100))
)

class Person(object):
    def __init__(self, firstname, lastname, email):
        if not firstname: raise ValueError("arg 'firstname' cannot be blank")
        if not lastname: raise ValueError("arg 'lastname' cannot be blank")
        self.firstname = firstname
        self.lastname = lastname
        self.email = email

    def __repr__(self):
        myclass = self.__class__.__name__
        return "<%s %s %s>" % (myclass, self.firstname, self.lastname)
        #return "<%s %s>" % (self.firstname, self.lastname)

    @property
    def name(self):
        return "%s %s" % (self.firstname, self.lastname)

    @classmethod
    def all(class_, order=None):
        """Return a Query of all Persons.  The caller can iterate this, do q.count(), add additional
        conditions, etc.
        """
        q = meta.Session.query(Person)
        if order and order.lower().startswith("d"):
            q = q.order_by([Person.birthdate.desc()])
        else:
            q = q.order_by([Person.lastname, Person.firstname])
        return q

    @classmethod
    def recent(self, cutoff_days=30):
        cutoff = datetime.date.today() - datetime.timedelta(days=cutoff_days)
        q = meta.Session.query(Person).order_by([Person.last_transaction_date.desc()])
        q = q.filter(Person.last_transaction_date >= cutoff)
        return q

class Address(object):
    pass

orm.mapper(Address, t_addresses)
orm.mapper(Person, t_people, properties = {
    'my_addresses' : orm.relation(Address, secondary = t_addresses_people),
    })
